package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class GenTestReport
 */
//@WebServlet(description = "產生測試報告", urlPatterns = { "/GenTestReport" })
public class GenTestReport extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GenTestReport() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String strSQL = "SELECT sourceid,mrid,mrdescription FROM tms_tpitem WHERE tpid=?";

		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String returnResult = "";
		ArrayList<TestReport> trs = new ArrayList<TestReport>();
		
		int maxSN = 0;
		int count = 0;
		int minSN = 0;
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			rs = ps.executeQuery();
	
			
			
			while (rs.next()) {
				
				String mrdescription =rs.getString("mrdescription");
				String mrid = rs.getString("mrid");
				String sourceid = rs.getString("sourceid");
				int nkp = 0;
				
				TestReport tr = new TestReport();
				tr.setMrdescription(mrdescription);
				tr.setSourceid(sourceid);
				
				strSQL = "SELECT sum(t2.numKeyPoint) nKP FROM tms_mrtcmapping t3, tms_testcase t2 WHERE t3.tcuid = t2.uID AND t3.tpid=? AND t3.mrid=?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs2 = ps.executeQuery();
				rs2.next();
				nkp = rs2.getInt("nKP");
				tr.setKeypointNum(nkp);
				
				
				strSQL = "SELECT COUNT(DISTINCT tcuid) FROM tms_mrtr where tpid=? AND mrid=?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs2 = ps.executeQuery();
				rs2.next();
				count = rs2.getInt(1);
				
				if (count >0) {
					strSQL = "SELECT MAX(innerTable.sn) FROM (SELECT sn FROM tms_mrtr where tpid=? AND mrid=? ) innerTable";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tpid);
					ps.setString(2, mrid);
					rs2 = ps.executeQuery();
					rs2.next();
					maxSN = rs2.getInt(1);
					minSN = maxSN - count;
					
					strSQL = "SELECT sum(t1.retenum) retenum, sum(succnum) succnum, sum(t1.failnum) failnum, sum(t1.cantnum) cantnum, sum(t1.waitnum) waitnum FROM activiti.tms_mrtr t1, activiti.tms_tpitem t2 where t1.tpid=? AND t2.mrid=? AND t1.sn>? AND t1.tpid = t2.tpid ;";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tpid);
					ps.setString(2, mrid);
					ps.setInt(3,minSN);
					rs2 = ps.executeQuery();
					rs2.next();
					
					tr.setCantNum(rs2.getInt("cantnum"));
					tr.setFailNum(rs2.getInt("failnum"));
					tr.setSuccNum(rs2.getInt("succnum"));
					tr.setWaitNum(rs2.getInt("waitnum"));
					
				} else {
					tr.setRetestNum(0);
					tr.setSuccNum(0);
					tr.setCantNum(0);
					tr.setWaitNum(nkp);
				}
				trs.add(tr);
				
			}
			
			returnResult = new Gson().toJson(trs);
			returnResult = returnResult.substring(1, returnResult.length());
			returnResult = "{\"sqlData\":["+returnResult+",\"strSQL\":\""+strSQL+"****"+tpid+"\",\"size\":\""+trs.size()+"\"}";
			
		} catch (SQLException e) {
			returnResult = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e, "", true)+"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (rs2!= null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		}
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);		
	}

}

class TestReport {

	private String mrdescription;
	private String comment;
	private String sourceid;
	private int retestNum;
	private int keypointNum;
	private int succNum;
	private int failNum; 
	private int cantNum;
	private int waitNum;
	
	public String getSourceid() {
		return sourceid;
	}
	public void setSourceid(String sourceid) {
		this.sourceid = sourceid;
	}
	public String getMrdescription() {
		return mrdescription;
	}
	public void setMrdescription(String mrdescription) {
		this.mrdescription = mrdescription;
	}
	public String getComment() {
		return comment;
	}
	public void setComment(String comment) {
		this.comment = comment;
	}
	public int getRetestNum() {
		return retestNum;
	}
	public void setRetestNum(int retestNum) {
		this.retestNum = retestNum;
	}
	public int getKeypointNum() {
		return keypointNum;
	}
	public void setKeypointNum(int keypoint) {
		this.keypointNum = keypoint;
	}
	public int getSuccNum() {
		return succNum;
	}
	public void setSuccNum(int succNum) {
		this.succNum = succNum;
	}
	public int getFailNum() {
		return failNum;
	}
	public void setFailNum(int failNum) {
		this.failNum = failNum;
	}
	public int getCantNum() {
		return cantNum;
	}
	public void setCantNum(int cantNum) {
		this.cantNum = cantNum;
	}
	public int getWaitNum() {
		return waitNum;
	}
	public void setWaitNum(int waitNum) {
		this.waitNum = waitNum;
	}
}
